﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using DatabaseHelper.Core.Models;
using DatabaseHelper.Core.Services.Interfaces;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
using System.IO;
using System.Linq;

namespace DatabaseHelper.Core.Services
{
    public class ExcelService : IExcelService
    {
        public void ExportDatabaseInfoExcel(string filename, string directory, List<ColumnViewEntity> columns)
        {
            #region 路径验证/生成
            if (!Directory.Exists(directory))
            {
                Directory.CreateDirectory(directory);
            }

            if (!filename.EndsWith(".xlsx"))
            {
                filename = $"{filename}.xlsx";
            }
            var filepath = Path.Combine(directory, filename);
            #endregion
            
            var tables = columns.GroupBy(a => a.TableName);

            #region 构建Excel
            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook))
            {
                WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
                workbookPart.Workbook = new Workbook() { Sheets = new Sheets() };

                Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook
                    .GetFirstChild<Sheets>();

                UInt32Value i = 1;
                foreach (var table in tables)
                {
                    var worksheetPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorksheetPart>();
                    
                    var columnsInTable = table.ToList();
                    SheetData sheetData = new SheetData();
                    FillSheetData(sheetData, columnsInTable);
                    worksheetPart.Worksheet = new Worksheet(sheetData);
                    
                    string relationShipId = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart);

                    Sheet sheet = new Sheet()
                    {
                        Id = relationShipId,
                        SheetId = i++,
                        Name = table.Key,
                    };


                    sheets.Append(sheet);
                }

                #endregion

                spreadsheetDocument.Close();

            }

        }
    
        private void FillSheetData(SheetData sheetData, List<ColumnViewEntity> tableColumns)
        {
            UInt32Value rowNumber = 1;
            // Header
            var rowHeader = GetSheetHeaderRow(rowNumber++);
            sheetData.Append(rowHeader);

            foreach (var col in tableColumns.OrderBy(a=>a.OrdinalPosition).ToList())
            {
                sheetData.Append(GetDataRow(col, rowNumber++));
            }
        }

        private Row GetSheetHeaderRow(UInt32Value index)
        {
            var row = new Row() { RowIndex = index };
            row.Append(GetCell("字段名"));
            row.Append(GetCell("数据类型"));
            row.Append(GetCell("主键"));
            row.Append(GetCell("是否为标识列(自增列)"));
            row.Append(GetCell("是否为Guid列"));
            row.Append(GetCell("允许为NULL"));
            row.Append(GetCell("说明"));
            return row;
        }

        private string[] NumericTypes = new string[]
        {
            "float",
            "decimal",
            "double",
            "numeric",
            "smallmoney",
            "money",
        };

        private Row GetDataRow(ColumnViewEntity columnViewEntity, UInt32Value index)
        {
            Row row = new Row() { RowIndex = index };
            row.Append(GetCell(columnViewEntity.ColumnName));

            // 判断数据类型
            if (columnViewEntity.DataType.Contains("char"))
            {
                var length = columnViewEntity.CharacterMaximumLength == "-1" ? "Max" : columnViewEntity.CharacterMaximumLength;
                row.Append(GetCell($"{columnViewEntity.DataType}({length})"));
            }else if (NumericTypes.Contains(columnViewEntity.DataType.ToLower()))
            {
                row.Append(GetCell($"{columnViewEntity.DataType}({columnViewEntity.NumericPrecision},{columnViewEntity.NumericScale})"));
            }
            else
            {
                row.Append(GetCell(columnViewEntity.DataType));
            }

            row.Append(GetCell(columnViewEntity.IsPrimaryKey ? "✔" : string.Empty));
            row.Append(GetCell(columnViewEntity.IsIdentity ? "是" : string.Empty));
            row.Append(GetCell(columnViewEntity.IsRowGuidCol ? "是" : string.Empty));
            row.Append(GetCell(columnViewEntity.IsNullable ? "✔" : string.Empty));
            row.Append(GetCell(columnViewEntity?.ColumnDescription ?? string.Empty));
            return row;
        }

        private Cell GetCell(string value, CellValues values = CellValues.String)
        {
            Cell cell = new Cell()
            {
                DataType = values
            };
            CellValue cellValue = new CellValue();
            cellValue.Text = value;
            cell.Append(cellValue);
            return cell;
        }
    }
}
